package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.datacenter.dto.DailyDataDto;
import com.dy.yunying.api.datacenter.vo.AdDataAnalysisVO;
import com.dy.yunying.api.datacenter.vo.DailyDataVO;
import com.dy.yunying.biz.config.YunYingProperties;
import com.dy.yunying.biz.utils.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * @author sunyq
 * @date 2022/6/21 17:02
 */
@Slf4j
@Component(value = "dailyDataDao")
public class DailyDataDao {
	@Resource(name = "dorisTemplate")
	private JdbcTemplate dorisTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	public List<DailyDataVO> list(DailyDataDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n" +
				"\t`day`,\n" +
				"\tuuidNums,-- 新增设备数\n" +
				"\taccountNums,-- 新增账号数 \n" +
				"\tround( IF ( uuidNums > 0, divide ( accountNums * 100.00, uuidNums ), 0 ), 2 )  AS regRate,-- 注册转换率  新增账号/新增设备\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate,-- 次留\n" +
				"\tactiveAccounts, -- 活跃账号\n" +
				"\tactiveFeeAccounts,-- 活跃付费总人数\n" +
				"\tactivePayAmounts,-- 付费总金额\n" +
				"\tround( IF ( activeAccounts > 0, divide ( activeFeeAccounts * 100.00, activeAccounts ), 0 ), 2 ) AS payFeeRate,-- 付费率 充值人数/活跃账号\n" +
				" \tround( IF ( activeAccounts > 0, divide ( activePayAmounts, activeAccounts ), 0 ), 2 ) AS arpu,-- ARPU 活跃充值金额/活跃账号\n" +
				"\tround( IF ( activeFeeAccounts > 0, divide ( activePayAmounts, activeFeeAccounts ), 0 ), 2 ) AS arppu,-- ARPPU  活跃充值金额/充值人数\n" +
				"\tnewPayNums,-- 新账号充值人数\n" +
				"\tnewPayAmounts,-- 新账号充值金额\n" +
				"\tround( IF ( accountNums > 0, divide ( newPayNums * 100, accountNums ), 0 ), 2 ) AS newPayFeeRate ,-- 新账号付费率 新账号充值人数/新增账号\n" +
				"\tround( IF ( accountNums > 0, divide ( newPayAmounts, accountNums ), 0 ), 2 ) AS newArpu,-- 新账号ARPU 新账号充值金额/新增账号\n" +
				"\tround( IF ( newPayNums > 0, divide ( newPayAmounts, newPayNums ), 0 ), 2 ) AS newArppu,-- 新账号ARPPU 新账号充值金额/新账号充值人数\n" +
				"\toldActiveAccounts,-- 老账号数\n" +
				"\toldActiveFeeAccounts,-- 老帐号充值人数\n" +
				"\toldActivePayAmounts,-- 老帐号充值金额\n" +
				"\tround( IF ( oldActiveAccounts > 0, divide ( oldActiveFeeAccounts * 100, oldActiveAccounts ), 0 ), 2 ) AS oldPayFeeRate,-- 老帐号付费率 老账号充值人数/老账号数\n" +
				"\tround( IF ( oldActiveAccounts > 0, divide ( oldActivePayAmounts, oldActiveAccounts ), 0 ), 2 ) AS oldArpu,-- 老账号ARPU 老账号充值金额/老账号数\n" +
				"\tround( IF ( oldActiveFeeAccounts > 0, divide ( oldActivePayAmounts,oldActiveFeeAccounts ), 0 ), 2 ) AS oldArppu -- 老账号ARPPU 老账号充值金额/老账号充值人数\n" +
				"FROM\n" +
				"(")
				.append(getBaseSql(req)).append(" ) a \n");
		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    day DESC\n");
		}

		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}
		log.info("每日运营数据分列表查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<DailyDataVO> dailyDataVOList = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(DailyDataVO.class));
		log.info("每日运营数据分列表页查询耗时: {}ms", System.currentTimeMillis() - start);
		return dailyDataVOList;
	}


	private StringBuilder getBaseSql(DailyDataDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n" +
				"\tIF(a.`day` is null,b.`day`,a.`day`\t) `day`,\n" +
				"\tCOALESCE(uuidNums,0) uuidNums,-- 新增设备数\n" +
				"\tCOALESCE(accountNums,0) accountNums,-- 新增账号数\n" +
				"\tCOALESCE(retentionNums,0) retentionNums, -- 次留数\n" +
				"\tCOALESCE(activeAccounts,0) activeAccounts, -- 活跃账号\n" +
				"\tCOALESCE(activeFeeAccounts,0) activeFeeAccounts,-- 活跃付费总人数\n" +
				"\tCOALESCE(activePayAmounts,0) activePayAmounts,-- 付费总金额\n" +
				"\tCOALESCE(newPayNums,0) newPayNums,-- 新账号充值人数\n" +
				"\tCOALESCE(newPayAmounts,0) newPayAmounts,-- 新账号充值金额\n" +
				"\tCOALESCE(activeAccounts,0)  - COALESCE(accountNums,0)  AS  oldActiveAccounts,-- 老账号数\n" +
				"\tCOALESCE(activeFeeAccounts,0) -  COALESCE(newPayNums,0)  AS oldActiveFeeAccounts,-- 老帐号充值人数\n" +
				"\tCOALESCE(activePayAmounts,0)  - COALESCE(newPayAmounts,0)  AS oldActivePayAmounts -- 老帐号充值金额\n"+
				"FROM\n" +
				"\t(\n" +
				"SELECT\n" +
				"IF(a.`day` is null,b.`day`,a.`day`\t) `day`,\n" +
				"uuidNums,-- 新增设备数\n" +
				"accountNums,-- 新增用户数\n" +
				"retentionNums -- 次留\n" +
				"\n" +
				"FROM\n" +
				"( SELECT a.reg_date as `day`, COUNT( DISTINCT uuid ) AS uuidNums -- 新增设备数 \n" +
				"\tFROM \n").append(yunYingProperties.getDailyDataDeviceRegTableData()).append(" a \n")
				.append("\t\t\twhere  ")
				.append(selectCommonCondition(req,1)).append("\n")
				.append("\t\tGROUP BY\n" +
						"\t\t\t`reg_date` \n" +
						"\t\t) a\n" +
						"\t\tFULL JOIN (\n" +
						"\t\tSELECT\n" +
						"\t\t\tIF(a.`day` is null,b.`day`,a.`day`\t) `day`,\n" +
						"\t\t\tretentionNums,-- 次留\n" +
						"\t\t\taccountNums -- 新增用户数\n" +
						"\t\t\t\n" +
						"\t\tFROM\n" +
						"\t\t\t(\n" +
						"\t\t\tSELECT\n" +
						"\t\t\t\tb.reg_date AS `day`,\n" +
						"\t\t\t\tCOUNT( DISTINCT b.username) AS retentionNums -- 次留\n" +
						"\t\t\tFROM\n" +
						"\t\t\t\t(\n" +
						"\t\t\t\tSELECT\n" +
						"\t\t\t\t\tactive_date,\n" +
						"\t\t\t\t\tusername,\n" +
						"\t\t\t\t\tapp_main, app_code \n" +
						"\t\t\t\tFROM \n")
				.append(yunYingProperties.getDailyDataActiveTableData()).append(" a \n")
				.append(" where ")
				.append(selectCommonCondition(req,2)).append("\n")
				.append(" ) a \n")
				.append("\t\t\tinner JOIN ")
				.append("( SELECT username, reg_date, app_main, app_code FROM \n")
				.append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a where \n")
				.append(selectCommonCondition(req,1)).append("\n")
				.append(") b ON a.app_main = b.app_main \n" +
						"\t\t\t\tAND a.username = b.username \n" +
						"\t\t\t\tAND a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 1 DAY )\n" +
						"\t\t\tGROUP BY\n" +
						"\t\t\t\tb.reg_date \n" +
						"\t\t\t) a\n" +
						"\t\t\tFULL JOIN (\n" +
						"\t\t\tSELECT\n" +
						"\t\t\t\tCOUNT( DISTINCT username ) AS accountNums,\n" +
						"\t\t\t\treg_date AS `day` \n" +
						"\t\t\tFROM \n")
				.append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a where ")
				.append(selectCommonCondition(req,1)).append("\n")
				.append("GROUP BY\n" +
						"\t\t\t\ta.reg_date \n" +
						"\t\t\t) b USING ( `day` ) \n" +
						"\t\t) b USING ( `day` ) \n" +
						"\t) a\n" +
						"\tFULL JOIN (\n" +
						"\tSELECT\n" +
						"\t\t`day`,\n" +
						"\t\tCOUNT( DISTINCT newpaynum ) AS newPayNums,-- 新账号充值人数\n" +
						"\t\tsum( newpayamount ) AS newPayAmounts,-- 新账号充值金额\n" +
						"\t\tCOUNT( DISTINCT paynum ) AS activeFeeAccounts,-- 活跃付费总人数\n" +
						"\t\tSUM( pay_amount ) AS activepayamounts,-- 付费总金额\n" +
						"\t\tCOUNT( DISTINCT usename ) activeaccounts -- 活跃账号\n" +
						"\t\t\n" +
						"\tFROM\n" +
						"\t\t(\n" +
						"\t\tSELECT\n" +
						"\t\t\ta.active_date AS DAY,\n" +
						"\t\t\ta.username usename,-- 活跃账号数,\n" +
						"\t\t\tpay_amount,-- 付费金额\n" +
						"\t\tIF\n" +
						"\t\t\t( pay_amount > 0, a.username, NULL ) paynum,-- 付费人数\n" +
						"\t\tIF\n" +
						"\t\t\t( a.active_date = b.reg_date AND a.pay_amount > 0, a.username, NULL ) newpaynum,-- 新付费人\n" +
						"\t\tIF\n" +
						"\t\t\t( a.active_date = b.reg_date AND a.pay_amount > 0, pay_amount, NULL ) newpayamount -- 新付费金额\n" +
						"\t\tFROM\n" +
						"\t\t\t(\n" +
						"\t\t\tSELECT\n" +
						"\t\t\t\tactive_date,\n" +
						"\t\t\t\tusername,\n" +
						"\t\t\t\tpay_amount, app_main, app_code  \n" +
						"\t\t\tFROM")
				.append("      "+ yunYingProperties.getDailyDataActiveTableData()).append("  a WHERE \n")
				.append(selectCommonCondition(req,3)).append("\n")
				.append(") a \n").append(StringUtils.isEmpty(req.getParentchlArr()) ? " left " : " inner ")
				.append("JOIN (SELECT reg_date,username , app_main, app_code from \n")
				.append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a WHERE ")
				.append(selectCommonCondition(req,4))
				.append(" ) b  ON  a.app_main = b.app_main  and  a.app_code = b.app_code and a.username = b.username\n")
				.append("\t\t) a \n" +
						"\tGROUP BY\n" +
						"\t\ta.`day` \n" +
						"\t) b USING ( `day` )  \n");
		return sql;
	}

	public String selectCommonCondition(DailyDataDto req,Integer num){
		String startTime = req.getStartTime();
		String endTime = req.getEndTime();
		String pgidArr = req.getPgidArr();
		String gameidArr = req.getGameidArr();
		String parentchlArr = req.getParentchlArr();
		String chlArr = req.getChlArr();
		String appchlArr = req.getAppchlArr();
		StringBuilder commonCondition = new StringBuilder();
		commonCondition.append(" a.dx_app_code = ")
				.append(yunYingProperties.getDxAPPCode())
				.append(StringUtils.SPACE);
		if (StringUtils.isNotEmpty(pgidArr)){
			commonCondition.append("and a.app_main in ( ").append(pgidArr).append(" )")
					.append(StringUtils.SPACE);
		}
		if (StringUtils.isNotEmpty(gameidArr)){
			commonCondition.append("and a.app_code in ( ").append(gameidArr).append(" )")
					.append(StringUtils.SPACE);
		}
		if (num == 1 ){
			commonCondition.append("and a.reg_date >= ").append("\'"+startTime+"\'")
					.append(StringUtils.SPACE)
					.append("and a.reg_date <= ").append("\'"+endTime+"\'")
					.append(StringUtils.SPACE);
		}  else if (num == 2) {
			String format = null;
			try {
				SimpleDateFormat formatter = new SimpleDateFormat( "yyyy-MM-dd");
				Date date = formatter.parse(endTime);
				Calendar c = Calendar.getInstance();
				c.setTime(date);
				c.add(Calendar.DAY_OF_MONTH, 1);
				Date time = c.getTime();
				format = formatter.format(time);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			if (StringUtils.isEmpty(format)){
				format = endTime;
			}
			commonCondition.append("and a.active_date >= ").append("\'"+startTime+"\'")
					.append(StringUtils.SPACE)
					.append("and a.active_date <= ").append("\'"+format+"\'")
					.append(StringUtils.SPACE);
			return commonCondition.toString();
		} else if (num == 3){
			commonCondition.append("and a.active_date >= ").append("\'"+startTime+"\'")
					.append(StringUtils.SPACE)
					.append("and a.active_date <= ").append("\'"+endTime+"\'")
					.append(StringUtils.SPACE);
			return commonCondition.toString();
		}else {
			if (StringUtils.isEmpty(parentchlArr)){
				commonCondition.append("and a.reg_date >= ").append("\'"+startTime+"\'")
						.append(StringUtils.SPACE)
						.append("and a.reg_date <= ").append("\'"+endTime+"\'")
						.append(StringUtils.SPACE);
			} else {
				commonCondition.append("and a.reg_date <= ").append("\'"+endTime+"\'")
						.append(StringUtils.SPACE);
			}

		}

		if (StringUtils.isNotEmpty(parentchlArr)){
			String[] split = parentchlArr.split(",");
			if (split !=null && split.length>0){
				commonCondition.append("and a.chl_main in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);

			}

		}
		if (StringUtils.isNotEmpty(chlArr)){
			String[] split = chlArr.split(",");
			if (split !=null && split.length>0){
				commonCondition.append("and a.chl_sub in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);

			}
		}
		if (StringUtils.isNotEmpty(appchlArr)){
			String[] split = appchlArr.split(",");
			if (split !=null && split.length>0){
				commonCondition.append("and a.chl_app in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
					.append(StringUtils.SPACE);
			}
		}
		return commonCondition.toString();
	}

	public Long count(DailyDataDto req) {
		final StringBuilder countSql = new StringBuilder();
		countSql.append("SELECT\n");
		countSql.append("    COUNT(1)\n");
		countSql.append("FROM\n");
		countSql.append("    (\n");
		countSql.append(getBaseSql(req));
		countSql.append("    ) a\n");
		return dorisTemplate.queryForObject(countSql.toString(), Long.class);
	}

	public List<DailyDataVO> collect(DailyDataDto req) {
		String sql = buildCollectSql(req);
		log.info("每日运营数据汇总SQL: [\n{}]", sql);
		long start = System.currentTimeMillis();
		List<DailyDataVO> dailyDataVOList = dorisTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(DailyDataVO.class));
		log.info("每日运营数据汇总SQL查询耗时: {}ms", System.currentTimeMillis() - start);
		return dailyDataVOList;
	}

	private String buildCollectSql(DailyDataDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("\t\tSELECT  '汇总' day, \n" +
				"\t\t\tSUM(uuidNums) as uuidNums, -- 新增设备数 \n" +
				"\t\t\tSUM(accountNums) as accountNums, -- 新增用户数 \n" +
				"\t\t\tSUM(regRate) as regRate, -- 注册转换率  新增账号/新增设备\n" +
				"\t\t\tSUM(retentionRate) as retentionRate, -- 次留\n" +
				"\t\t\tSUM(payFeeRate) as payFeeRate, -- 付费率 充值人数/活跃账号\n" +
				"\t\t\tSUM(arpu) as arpu, -- ARPU 活跃充值金额/活跃账号\n" +
				"\t\t\tSUM(arppu) as arppu, -- ARPPU  活跃充值金额/充值人数\n" +
				"\t\t\tSUM(newPayNums) as newPayNums,-- 新账号充值人数\n" +
				"\t\t\tSUM(newPayAmounts) as newPayAmounts,-- 新账号充值金额\n" +
				"\t\t\tSUM(activeFeeAccounts) as activeFeeAccounts,-- 活跃付费总人数\n" +
				"\t\t\tSUM(activePayAmounts) as activePayAmounts,-- 付费总金额\n" +
				"\t\t\tSUM(activeAccounts) as activeAccounts, -- 活跃账号\n" +
				"\t\t\tSUM(newPayFeeRate) as newPayFeeRate ,-- 新账号付费率 新账号充值人数/新增账号\n" +
				"\t\t\tSUM(newArpu) as newArpu,-- 新账号ARPU 新账号充值金额/新增账号\n" +
				"\t\t\tSUM(newArppu) as newArppu,-- 新账号ARPPU 新账号充值金额/新账号充值人数\n" +
				"\t\t\tSUM(oldActiveAccounts) as oldActiveAccounts,-- 老账号数\n" +
				"\t\t\tSUM(oldActiveFeeAccounts) as oldActiveFeeAccounts,-- 老帐号充值人数\n" +
				"\t\t\tSUM(oldActivePayAmounts) as oldActivePayAmounts, -- 老帐号充值金额\n" +
				"\t\t\tSUM(oldPayFeeRate) as oldPayFeeRate,-- 老帐号付费率 老账号充值人数/老账号数\n" +
				"\t\t\tSUM(oldArpu) as oldArpu,-- 老账号ARPU 老账号充值金额/老账号数\n" +
				"\t\t\tSUM(oldArppu) as oldArppu -- 老账号ARPPU 老账号充值金额/老账号充值人数 \n" +
				"\t\t\tFROM\n" +
				"\t\t\t(\n" +
				"\t\t\tSELECT  \n" +
				"\t\t\tSUM(uuidNums)  as uuidNums, -- 新增设备数 \n" +
				"\t\t\tSUM(accountNums) AS accountNums, -- 新增用户数 \n" +
				"\t\t\tround( IF ( SUM(uuidNums) > 0, divide (SUM(accountNums) * 100.00,  SUM(uuidNums) ), 0 ), 2 )  AS regRate,-- 注册转换率  新增账号/新增设备\n" +
				"       0 AS retentionRate,-- 次留\n"+
				"\t\t\tround( IF ( SUM(activeAccounts) > 0, divide ( SUM(activeFeeAccounts) * 100.00, SUM(activeAccounts) ), 0 ), 2 ) AS payFeeRate,-- 付费率 充值人数/活跃账号\n" +
				"\t\t\tround( IF ( SUM(activeAccounts) > 0, divide ( SUM(activePayAmounts), SUM(activeAccounts) ), 0 ), 2 ) AS arpu,-- ARPU 活跃充值金额/活跃账号\n" +
				"\t\t\tround( IF ( SUM(activeFeeAccounts) > 0, divide ( SUM(activePayAmounts), SUM(activeFeeAccounts) ), 0 ), 2 ) AS arppu,-- ARPPU  活跃充���金额/充值人数\n" +
				"\t\t\tSUM(newPayNums) AS newPayNums,-- 新账号充值人数\n" +
				"\t\t\tSUM(newPayAmounts) AS newPayAmounts,-- 新账号充值金额\n" +
				"\t\t\tSUM(activeFeeAccounts) AS activeFeeAccounts,-- 活跃付费总人数\n" +
				"\t\t\tSUM(activePayAmounts) AS activePayAmounts,-- 付费总金额\n" +
				"\t\t\tSUM(activeAccounts) AS activeAccounts, -- 活跃账号\n" +
				"\t\t\tround( IF ( SUM(accountNums) > 0, divide ( SUM(newPayNums) * 100, SUM(accountNums) ), 0 ), 2 ) AS newPayFeeRate ,-- 新账号付费率 新账号充值人数/新增账号\n" +
				"\t\t\tround( IF ( SUM(accountNums) > 0, divide ( SUM(newPayAmounts), SUM(accountNums) ), 0 ), 2 ) AS newArpu,-- 新账号ARPU 新账号充值金额/新增账号\n" +
				"\t\t\tround( IF ( SUM(newPayNums) > 0, divide ( SUM(newPayAmounts), SUM(newPayNums) ), 0 ), 2 ) AS newArppu,-- 新账号ARPPU 新账号充值金额/新账号充值人数\n" +
				"\t\t\tSUM(activeAccounts)- SUM(accountNums)  AS  oldActiveAccounts,-- 老账号数\n" +
				"\t\t\tSUM(activeFeeAccounts) -  SUM(newPayNums)  AS oldActiveFeeAccounts,-- 老帐号充值人数\n" +
				"\t\t\tSUM(activePayAmounts)  - SUM(newPayAmounts)  AS oldActivePayAmounts, -- 老帐号充值金额\n" +
				"\t\t\tround( IF ( SUM(activeAccounts)- SUM(accountNums) > 0, divide ( (SUM(activeFeeAccounts) -  SUM(newPayNums)) * 100, SUM(activeAccounts)- SUM(accountNums) ), 0 ), 2 ) AS oldPayFeeRate,-- 老帐号付费率 老账号充值人数/老账号数\n" +
				"\t\t\tround( IF ( SUM(activeAccounts)- SUM(accountNums) > 0, divide ( (SUM(activePayAmounts)  - SUM(newPayAmounts)) , SUM(activeAccounts)- SUM(accountNums) ), 0 ), 2 ) AS oldArpu,-- 老账号ARPU 老账号充值金额/老账号数\n" +
				"\t\t\tround( IF ( SUM(activeFeeAccounts) -  SUM(newPayNums) > 0, divide ( SUM(activePayAmounts)  - SUM(newPayAmounts),SUM(activeFeeAccounts) -  SUM(newPayNums) ), 0 ), 2 ) AS oldArppu -- 老账号ARPPU 老账号充值金额/老账号充值人数\n" +
				"\t\tFROM \t\t\t\n" +
				"\t\t\t\t(\n" +
				"\t\t\t\t\n" +
				"\t\t\t\tSELECT \n" +
				"\t\t\t\t\tCOUNT( DISTINCT uuid ) AS uuidNums, -- 新增设备数 \n" +
				"\t\t\t\t\t0 AS accountNums, -- 新增用户数 \n" +
				"\t\t\t\t\t0 AS newPayNums,-- 新账号充值人数\n" +
				"          0 AS newPayAmounts,-- 新账号充值金额\n" +
				"          0 AS activeFeeAccounts,-- 活跃付费总人数\n" +
				"          0 AS activePayAmounts,-- 付费总金额\n" +
				"          0 AS activeAccounts -- 活跃账号\n" +
				"        FROM \n")
				.append("\t\t\t\t\t\t").append(yunYingProperties.getDailyDataDeviceRegTableData()).append("  a \n")
				.append("         where ")
				.append("         ").append(selectCommonCondition(req,1)).append(" \n")
				.append("          UNION ALL \n" +
						"              \n" +
						"\t\t\t\tSELECT\n" +
						"\t\t\t\t\t\t0 AS uuidNums, -- 新增设备数 \n" +
						"\t\t\t\t\t\tCOUNT( DISTINCT username ) AS accountNums, -- 新增用户数 \n" +
						"\t\t\t\t\t\t0 AS newPayNums,-- 新账号充值人数\n" +
						"\t\t\t\t\t\t0 AS newPayAmounts,-- 新账号充值金额\n" +
						"\t\t\t\t\t\t0 AS activeFeeAccounts,-- 活跃付费总人数\n" +
						"\t\t\t\t\t\t0 AS activePayAmounts,-- 付费总金额\n" +
						"\t\t\t\t\t\t0 AS activeAccounts -- 活跃账号\n" +
						"\t\t\t\tFROM \n")
				.append("          ").append(yunYingProperties.getDailyDataAccountRegTableData()).append("  a \n")
				.append("                where ")
				.append("              ").append(selectCommonCondition(req,1)).append("\n")
				.append("               UNION ALL        \n" +
						"                     SELECT\n" +
						"                         0 AS uuidNums, -- 新增设备数 \n" +
						"                         0 AS accountNums, -- 新增用户数 \n" +
						"                         COUNT( DISTINCT newpaynum ) AS newPayNums,-- 新账号充值人数\n" +
						"                         sum( newpayamount ) AS newPayAmounts,-- 新账号充值金额\n" +
						"                         COUNT( DISTINCT paynum ) AS activeFeeAccounts,-- 活跃付费总人数\n" +
						"                         SUM( pay_amount ) AS activePayAmounts,-- 付费总金额\n" +
						"                         COUNT( DISTINCT usename ) activeAccounts -- 活跃账号 \n" +
						"                         FROM\n" +
						"                         (" +
						"                           SELECT\n" +
						"                                  a.active_date AS DAY,\n" +
						"                                  a.username usename,-- 活跃账号数,\n" +
						"                                  pay_amount,-- 付费金额\n" +
						"                         		IF( pay_amount > 0, a.username, NULL ) paynum,-- 付费人数\n" +
						"						  		IF( a.active_date = b.reg_date AND a.pay_amount > 0, a.username, NULL ) newpaynum,-- 新付费人\n" +
						"						  		IF( a.active_date = b.reg_date AND a.pay_amount > 0, pay_amount, NULL ) newpayamount -- 新付费金额\n" +
						"                            FROM \n" +
						"                                  (SELECT \n" +
						"                                          active_date, \n" +
						"                                          username, \n" +
						"                                          pay_amount, app_main, app_code   \n" +
						"                                  FROM  \n")
				.append("                       ").append(yunYingProperties.getDailyDataActiveTableData()).append( " a  where ").append("\n")
				.append("                      ").append(selectCommonCondition(req,3)).append("\n")
				.append("                      ) a   ")
				.append(StringUtils.isEmpty(req.getParentchlArr()) ? " left " : " inner ").append("JOIN (SELECT reg_date,username , app_main, app_code from \n")
				.append("                      ").append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a WHERE \n")
				.append("                         ").append(selectCommonCondition(req,4))
				.append(") b  ON  a.app_main = b.app_main and a.app_code = b.app_code and a.username = b.username\n")
				.append("          ) a\n" +
						"\t\t\t\t) a \n" +
						"          UNION ALL\n" +
						"            SELECT \n" +
						"                0  as uuidNums, -- 新增设备数 \n" +
						"                0 AS accountNums, -- 新增用户数\n" +
						"                0 AS regRate,-- 注册转换率  新增账号/新增设备\n" +
						"                round( IF ( SUM(accountNums) > 0, divide ( SUM(retentionNums)  * 100.00, SUM(accountNums) ), 0 ), 2 ) AS retentionRate,-- 次留\n" +
						"                0 payFeeRate,-- 付费率 充值人数/活跃账号\n" +
						"                0 AS arpu,-- ARPU 活跃充值金额/活跃账号\n" +
						"                0 AS arppu,-- ARPPU  活跃充���金额/充值人数\n" +
						"                0 AS newPayNums,-- 新账号充值人数\n" +
						"                0 AS newPayAmounts,-- 新账号充值金额\n" +
						"                0 AS activeFeeAccounts,-- 活跃付费总人数\n" +
						"                0 AS activePayAmounts,-- 付费总金额\n" +
						"                0 AS activeAccounts, -- 活跃账号\n" +
						"                0 AS newPayFeeRate ,-- 新账号付费率 新账号充值人数/新增账号\n" +
						"                0 AS newArpu,-- 新账号ARPU 新账号充值金额/新增账号\n" +
						"                0 AS newArppu,-- 新账号ARPPU 新账号充值金额/新账号充值人数\n" +
						"                0  AS  oldActiveAccounts,-- 老账号数\n" +
						"                0  AS oldActiveFeeAccounts,-- 老帐号充值人数\n" +
						"                0  AS oldActivePayAmounts, -- 老帐号充值金额\n" +
						"                0 AS oldPayFeeRate,-- 老帐号付费率 老账号充值人数/老账号数\n" +
						"                0 AS oldArpu,-- 老账号ARPU 老账号充值金额/老账号数\n" +
						"                0 AS oldArppu -- 老账号ARPPU 老账号充值金额/老账号充值人数\n" +

						"\t\t\t\t\t\t\t\tFROM\n" +
						"\t\t\t\t\n" +
						"                (SELECT\n" +
						"                       \n" +
						"                        retentionNums,-- 次留\n" +
						"                        accountNums -- 新增用户数\n" +
						"\n" +
						"                FROM\n" +

						"                        (\n" +
						"                        SELECT\n" +
						"                                b.reg_date AS `day`,\n" +
						"                                COUNT( DISTINCT b.username) AS retentionNums -- 次留\n" +
						"                        FROM\n" +
						"                            (\n" +
						"                                SELECT\n" +
						"                                    active_date,\n" +
						"                                    username,\n" +
						"                                    app_main, app_code \n" +
						"                                FROM \n" )
				.append("                   ").append(yunYingProperties.getDailyDataActiveTableData()).append("  a  \n")
				.append("                      ").append(" where \n")
				.append("                      ").append(selectCommonCondition(req,2)).append("\n")
				.append("                       ) a \n")
				.append("                         inner JOIN ")
				.append("                        ( SELECT username, reg_date, app_main, app_code FROM \n")
				.append("                           ").append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a  where \n")
				.append("                             ").append(selectCommonCondition(req,1)).append("\n")
				.append("                           ) b ON a.app_main = b.app_main \n" +
						"                                AND a.username = b.username \n" +
						"                                AND a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 1 DAY )\n" +
						"                             GROUP BY\n" +
						"                                b.reg_date \n" +
						"                             ) a\n" +
						"                    FULL JOIN (\n" +
						"                        SELECT\n" +
						"                            COUNT( DISTINCT username ) AS accountNums,\n" +
						"                            reg_date AS `day` \n" +
						"                        FROM \n" )
				.append("                          ").append(yunYingProperties.getDailyDataAccountRegTableData()).append("  a where \n")
				.append("                              ").append(selectCommonCondition(req,1))
				.append("                      GROUP BY\n" +
				"                                a.reg_date \n" +
				"                                     ) b USING ( `day` ) )  a  ) a \n");
		return sql.toString();
	}
}
